package net.zoneland.zrdp.common.utils.poi;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.math.BigDecimal;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.text.DecimalFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.IntStream;
import java.util.stream.Stream;
import javax.servlet.http.HttpServletResponse;

import net.zoneland.zrdp.common.utils.DateUtil;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.ListUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.RegExUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ReflectionUtils;

import net.zoneland.zrdp.common.annotation.Excel;
import net.zoneland.zrdp.common.annotation.Excels;
import net.zoneland.zrdp.common.annotation.Excel.ColumnType;
import net.zoneland.zrdp.common.annotation.Excel.Type;
import net.zoneland.zrdp.common.core.domain.AjaxResult;
import net.zoneland.zrdp.common.core.text.Convert;
import net.zoneland.zrdp.common.exception.UtilException;
import net.zoneland.zrdp.common.utils.DictUtils;
import net.zoneland.zrdp.common.utils.file.FileTypeUtils;
import net.zoneland.zrdp.common.utils.file.FileUtils;
import net.zoneland.zrdp.common.utils.file.ImageUtils;
import net.zoneland.zrdp.common.utils.reflect.ReflectUtils;

/**
 * Excel相关处理；主要用于系统自带的excel简单的导入和导出。复杂的还需要各自重写（如有合并表格的导入、导出）。
 *
 * @author zonevue
 */
public class ExcelProcessor<T>
{
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelProcessor.class);

    private static final String FORMULA_REGEX_STR = "[=+@-]";

    private static final String[] FORMULA_STR = { "=", "-", "+", "@" };

    /**
     * 用于dictType属性数据存储，避免重复查缓存
     */
    private final Map<String, String> sysDictMap = new HashMap<>();

    /**
     * Excel sheet最大行数，默认65536
     */
    public static final int SHEET_SIZE = 65536;

    /**
     * 工作表名称
     */
    private String sheetName;

    /**
     * 导出类型（EXPORT:导出数据；IMPORT：导入模板）
     */
    private Type type;

    /**
     * 工作薄对象
     */
    private Workbook wb;

    /**
     * 工作表对象
     */
    private Sheet sheet;

    /**
     * 样式列表
     */
    private Map<String, CellStyle> styles;

    /**
     * 导入导出数据列表
     */
    private List<T> list;

    /**
     * 注解列表
     */
    private List<Object[]> fields;

    /**
     * 当前行号
     */
    private int rownum;

    /**
     * 标题
     */
    private String title;

    /**
     * 最大高度
     */
    private short maxHeight;

    /**
     * 合并后最后行数
     */
    private int subMergedLastRowNum = 0;

    /**
     * 合并后开始行数
     */
    private int subMergedFirstRowNum = 1;

    /**
     * 对象的子列表方法；通过注解和类型是否是Collection类型或子类型来获取（有bug，当有多个子列表的时候是不支持的，只支持一个excel导出子方法）
     */
    private Method subMethod;

    /**
     * 对象的子列表属性
     */
    private List<Field> subFields;

    /**
     * 统计列表
     */
    private final Map<Integer, Double> statistics = new HashMap<>();

    /**
     * 数字格式
     */
    private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");

    /**
     * 实体对象
     */
    private final Class<T> clazz;

    /**
     * 需要排除列属性
     */
    private String[] excludeFields;

    public ExcelProcessor(Class<T> clazz)
    {
        this.clazz = clazz;
    }

    /**
     * 隐藏Excel中列属性
     *
     * @param fields 列属性名 示例[单个"name"/多个"id","name"]
     */
    public void hideColumn(String... fields)
    {
        this.excludeFields = fields;
    }

    public void init(final List<T> list, final String sheetName, final String title, final Type type) {
        this.list = ListUtils.emptyIfNull(list);
        this.sheetName = sheetName;
        this.type = type;
        this.title = title;
        createExcelField();
        createWorkbook();
        createTitle();
        createSubHead();
    }

    /**
     * 创建excel第一行标题
     */
    public void createTitle()
    {
        if (StringUtils.isNotEmpty(title)) {
            subMergedFirstRowNum++;
            subMergedLastRowNum++;
            final int fieldsMaxIndex = this.fields.size() - 1;
            final int titleLastCol = isSubList() ? fieldsMaxIndex + subFields.size() - 1 : fieldsMaxIndex;
            final Row titleRow = sheet.createRow(rownum == 0 ? rownum++ : 0);
            titleRow.setHeightInPoints(30);
            final Cell titleCell = titleRow.createCell(0);
            titleCell.setCellStyle(styles.get("title"));
            titleCell.setCellValue(title);
            sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(), titleLastCol));
        }
    }

    /**
     * 创建对象的子列表名称
     */
    public void createSubHead()
    {
        if (isSubList()) {
            subMergedFirstRowNum++;
            subMergedLastRowNum++;
            final Row subRow = sheet.createRow(rownum);
            int excelNum = 0;
            int subCellIndex = 0;
            for (final Object[] objects : fields) {
                final Field field = (Field) objects[0];
                final Excel attr = (Excel) objects[1];
                final Cell headCell1 = subRow.createCell(excelNum);
                headCell1.setCellValue(attr.name());
                headCell1.setCellStyle(styles.get(String.format("header_%s_%s", attr.headerColor(), attr.headerBackgroundColor())));
                if (Collection.class.isAssignableFrom(field.getType())) {
                    subCellIndex = excelNum;
                    excelNum = excelNum + subFields.size() - 1;
                }
                excelNum++;
            }

            final int headLastRow = subCellIndex + subFields.size() - 1;
            if (headLastRow > subCellIndex) {
                sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, subCellIndex, headLastRow));
            }
            rownum++;
        }
    }

    /**
     * 对excel表单默认第一个索引名转换成list
     *
     * @param is 输入流 WorkbookFactory会关闭输入流
     * @return 转换后集合
     */
    public List<T> importExcel(final InputStream is) throws Exception {
        return importExcel(is, 0);
    }

    /**
     * 对excel表单默认第一个索引名转换成list
     *
     * @param is 输入流 WorkbookFactory会关闭输入流
     * @param titleNum 标题占用行数
     * @return 转换后集合
     */
    public List<T> importExcel(final InputStream is, final int titleNum) throws Exception {
        return importExcel(StringUtils.EMPTY, is, titleNum);
    }

    /**
     * 对excel表单指定表格索引名转换成list
     *
     * @param sheetName 表格索引名
     * @param titleNum 标题占用行数
     * @param is 输入流 WorkbookFactory会关闭输入流
     * @return 转换后集合
     */
    public List<T> importExcel(final String sheetName, final InputStream is, final int titleNum) throws Exception
    {
        this.type = Type.IMPORT;
        this.wb = WorkbookFactory.create(is);

        // 如果指定sheet名,则取指定sheet中的内容 否则默认指向第1个sheet
        final Sheet currentSheet = StringUtils.isNotEmpty(sheetName) ? wb.getSheet(sheetName) : wb.getSheetAt(0);
        if (currentSheet == null) {
            throw new IOException("文件sheet不存在");
        }
        // 获取最后一个非空行的行下标，比如总行数为n，则返回的为n-1
        final int rows = currentSheet.getLastRowNum();
        if (rows < 1) { // 少于2行的excel 不用处理，因为至少需要一行字段名称（表头）和一行数据。
            return Collections.emptyList();
        }

        // 获取表头
        final Row head = currentSheet.getRow(titleNum);
        // 定义一个map用于存放excel 表头行的 字段名和列的序号.
        final Map<String, Integer> cellMap = getCellMap(head);
        if (cellMap.isEmpty()) {
            LOGGER.warn("没有读取到excel表头信息 ,表头行号:{}", titleNum);
            return Collections.emptyList();
        }
        // 得到类的所有能和cellMap 对应上的 field.
        final Map<Integer, Object[]> fieldsMap = getFieldMap(cellMap);
        if (fieldsMap.isEmpty()) {
            LOGGER.warn("通过excel表头和class注解没有匹配到任何字段");
            return Collections.emptyList();
        }

        final List<T> dataList = new ArrayList<>();
        // 下面开始正式获取数据了。
        for (int i = titleNum + 1; i <= rows; i++) {
            // 从表头下一行行开始取数据,默认第一行是表头.
            final Row row = currentSheet.getRow(i);
            // 判断当前行是否是空行
            if (isRowEmpty(row)) {
                continue;
            }
            // 新建实例.
            final T entity = createEntity(fieldsMap, row, currentSheet);
            dataList.add(entity);
        }

        return dataList;
    }

    private T createEntity(final Map<Integer, Object[]> fieldsMap, final Row row, final Sheet currentSheet) throws Exception {
        final T entity = clazz.getDeclaredConstructor().newInstance();
        for (final Map.Entry<Integer, Object[]> entry : fieldsMap.entrySet()) {

            // 从map中得到对应列的field.
            final Field field = (Field) entry.getValue()[0];
            final Excel attr = (Excel) entry.getValue()[1];
            // 获取属性名
            final String propertyName = getPropertyName(field, attr);

            Object val = this.getCellValue(row, entry.getKey());
            // 根据class字段属性,并根据字段属性的类型设置值.
            val = getNewVal(val, field);
            // 根据class注解 对cell 的值再次转化。
            val = getNewValByAttr(val, attr);
            // 如果是注解是图片类型
            if (ColumnType.IMAGE == attr.cellType() ) {
                final Map<String, PictureData> pictures = wb instanceof HSSFWorkbook ?
                    getSheetPictures03((HSSFSheet) currentSheet, (HSSFWorkbook) wb) : getSheetPictures07((XSSFSheet) currentSheet);
                if (pictures != null && !pictures.isEmpty()) {
                    final PictureData image = pictures.get(row.getRowNum() + "_" + entry.getKey());
                    if (image == null) {
                        val = "";
                    } else {
                        byte[] data = image.getData();
                        val = FileUtils.writeImportBytes(data);
                    }
                }
            }

            // 给实体bean 属性赋值
            ReflectUtils.invokeSetter(entity, propertyName, val);

        }
        return entity;
    }
    private String getPropertyName(final Field field, final Excel attr) {
        // 如果是这种情况
        // @Excel(name = "部门名称", targetAttr = "deptName")
        // private SysDept dept
        // 属性名为 dept.deptName
        if (StringUtils.isNotEmpty(attr.targetAttr())) {
            return field.getName() + "." + attr.targetAttr();
        }
        return field.getName();
    }
    /**
     * 根据class注解 对cell 的值再次转化。
     * @param val 传入值
     * @param attr 注解信息
     * @return 新的值
     */
    private Object getNewValByAttr(final Object val, final Excel attr) {
        Object newVal = val;
        if (StringUtils.isNotEmpty(attr.readConverterExp())) {
            // 简单 @Excel(name = "帐号状态", readConverterExp = "0=正常,1=停用") 这样的类型进行转换
            newVal = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());
        } else if (StringUtils.isNotEmpty(attr.dictType())) {
            // 数据字典类型转换
            newVal = reverseDictByExp(Convert.toStr(val), attr.dictType(), attr.separator());
        } else if (!attr.handler().equals(ExcelHandlerAdapter.class)) {
            // 自定义数据处理器
            newVal = dataFormatHandlerAdapter(val, attr);
        }
        return newVal;
    }
    /**
     * 根据class 字段的类型来对cell的值做初次转换。
     * @param val cell的原始值
     * @param field class 字段信息
     * @return 转换后的值
     */
    private Object getNewVal(final Object val, final Field field) {
        final Class<?> fieldType = normalizeToWrapperType(field.getType());

        Object newVal = val;
        if (String.class.equals(fieldType)) {
            newVal = handleStringConversion(field, val);
        } else if (Integer.class.equals(fieldType) && isNumericValue(val)) {
            newVal = Convert.toInt(val);
        } else if (Long.class.equals(fieldType) && isNumericValue(val)) {
            newVal = Convert.toLong(val);
        } else if (Double.class.equals(fieldType)) {
            newVal = Convert.toDouble(val);
        } else if (Float.class.equals(fieldType)) {
            newVal = Convert.toFloat(val);
        } else if (BigDecimal.class.equals(fieldType)) {
            newVal = Convert.toBigDecimal(val);
        } else if (Date.class.equals(fieldType)) {
            newVal = handleDateConversion(val);
        } else if (Boolean.class.equals(fieldType)) {
            newVal = Convert.toBool(val, false);
        }

        return newVal;
    }

    private Class<?> normalizeToWrapperType(Class<?> type) {
        if (type == int.class) return Integer.class;
        if (type == long.class) return Long.class;
        if (type == double.class) return Double.class;
        if (type == float.class) return Float.class;
        if (type == boolean.class) return Boolean.class;
        // ... handle other primitives
        return type;
    }

    private boolean isNumericValue(Object value) {
        return StringUtils.isNumeric(Convert.toStr(value));
    }

    private Object handleStringConversion(Field field, Object value) {
        String dateFormat = field.getAnnotation(Excel.class).dateFormat();
        return StringUtils.isNotEmpty(dateFormat) ? parseDateToStr(dateFormat, value) : Convert.toStr(value);
    }

    private Object handleDateConversion(Object value) {
        if (value instanceof String) {
            return DateUtil.parseDate(value);
        } else if (value instanceof Double) {
            return org.apache.poi.ss.usermodel.DateUtil.getJavaDate((Double) value);
        }
        return value;
    }

    /**
     * 通过excel的 表头 row 获取到对应字段名所在的列index
     * @param head 表头row
     * @return 列名对应index 的map
     */
    private Map<String, Integer> getCellMap(final Row head) {
        // 定义一个map用于存放excel列的序号和field.
        final Map<String, Integer> cellMap = new HashMap<>();
        // 获取表头
        for (int i = 0; i < head.getPhysicalNumberOfCells(); i++) {

            final Cell cell = head.getCell(i);
            if (Objects.nonNull(cell)) {
                final String value = this.getCellValue(cell).toString();
                cellMap.put(value, i);
            }

        }
        return cellMap;
    }

    /**
     * class 字段中excel注解和 当前excel表头信息对比获取到能匹配上的数据
     * @param cellMap excel文件表头对照map
     * @return class字段对照map
     */
    private Map<Integer, Object[]> getFieldMap(final Map<String, Integer> cellMap) {
        final List<Object[]> classFields = this.getFields();
        final Map<Integer, Object[]> fieldsMap = new HashMap<>();
        for (final Object[] objects : classFields) {
            final Excel attr = (Excel) objects[1];
            final Integer column = cellMap.get(attr.name());
            if (column != null) {
                fieldsMap.put(column, objects);
            }
        }
        return fieldsMap;
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单, 字段顺序按class bean 中 @Excel 的顺序属性，没有设置的话，按@Excel 出现的先后顺序。
     *
     * @param list 导出数据集合
     * @param sheetName 工作表的名称
     * @return 结果
     */
    public AjaxResult exportExcel(final List<T> list, final String sheetName) {
        return exportExcel(list, sheetName, StringUtils.EMPTY);
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单, 字段顺序按class bean 中 @Excel 的顺序属性，没有设置的话，按@Excel 出现的先后顺序。
     *
     * @param list 导出数据集合
     * @param sheetName 工作表的名称
     * @param title 标题
     * @return 结果
     */
    public AjaxResult exportExcel(final List<T> list, final String sheetName, final String title) {
        this.init(list, sheetName, title, Type.EXPORT);
        return exportExcel();
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param response 返回数据
     * @param list 导出数据集合
     * @param sheetName 工作表的名称
     */
    public void exportExcel(final HttpServletResponse response, final List<T> list, final String sheetName) {
        exportExcel(response, list, sheetName, StringUtils.EMPTY);
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param response 返回数据
     * @param list 导出数据集合
     * @param sheetName 工作表的名称
     * @param title 标题
     */
    public void exportExcel(final HttpServletResponse response, final List<T> list, final String sheetName, final String title) {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        this.init(list, sheetName, title, Type.EXPORT);
        exportExcel(response);
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param sheetName 工作表的名称
     * @return 结果
     */
    public AjaxResult importTemplateExcel(final String sheetName) {
        return importTemplateExcel(sheetName, StringUtils.EMPTY);
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param sheetName 工作表的名称
     * @param title 标题
     * @return 结果
     */
    public AjaxResult importTemplateExcel(final String sheetName, final String title) {
        this.init(null, sheetName, title, Type.IMPORT);
        return exportExcel();
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param sheetName 工作表的名称
     */
    public void importTemplateExcel(final HttpServletResponse response, final String sheetName) {
        importTemplateExcel(response, sheetName, StringUtils.EMPTY);
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @param sheetName 工作表的名称
     * @param title 标题
     */
    public void importTemplateExcel(final HttpServletResponse response, final String sheetName, final String title) {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        this.init(null, sheetName, title, Type.IMPORT);
        exportExcel(response);
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     */
    public void exportExcel(final HttpServletResponse response) {
        try {
            writeSheet();
            wb.write(response.getOutputStream());
        } catch (final Exception e) {
            LOGGER.error("导出Excel异常", e);
        } finally {
            IOUtils.closeQuietly(wb);
        }
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @return 结果
     */
    public AjaxResult exportExcel() {
        final String filename = encodingFilename(sheetName);
        try (final OutputStream out = Files.newOutputStream(Paths.get(getAbsoluteFile(filename)))) {
            writeSheet();
            wb.write(out);
            return AjaxResult.success(filename);
        } catch (final IOException e) {
            throw new UtilException("导出Excel失败，请联系网站管理员!" + filename, e);
        } finally {
            IOUtils.closeQuietly(wb);
        }
    }

    /**
     * 创建写入数据到Sheet
     */
    public void writeSheet() {
        final int totalSheets = calculateTotalSheets();
        for (int index = 0; index < totalSheets; index++) {
            createSheet(totalSheets, index);
            writeHeadersToSheet();
            if (Type.EXPORT.equals(type)) {
                fillExcelData(index);
                addStatisticsRow();
            }
        }
    }

    private int calculateTotalSheets() {
        return Math.max(1, (int) Math.ceil((double) list.size() / SHEET_SIZE));
    }

    private void writeHeadersToSheet() {
        final Row row = sheet.createRow(rownum);
        int column = 0;
        for (final Object[] os : fields) {
            final Field field = (Field) os[0];
            final Excel excel = (Excel) os[1];
            if (Collection.class.isAssignableFrom(field.getType())) {
                for (Field subField : subFields) {
                    final Excel subExcel = subField.getAnnotation(Excel.class);
                    createHeadCell(subExcel, row, column++);
                }
            } else {
                createHeadCell(excel, row, column++);
            }
        }
    }


    private static final int FIRST_ROW_OFFSET = 1;
    /**
     * 填充excel数据（注：list 太大的话可能造成内存的不足）
     *
     * @param index 序号
     */
    public void fillExcelData(final int index) {
        final int startNo = index * SHEET_SIZE;
        final int endNo = Math.min(startNo + SHEET_SIZE, list.size());

        int rowNo = (FIRST_ROW_OFFSET + rownum) - startNo;

        for (int i = startNo; i < endNo; i++) {
            rowNo = calculateRowNo(i, rowNo, startNo);
            final Row row = sheet.createRow(rowNo);
            final T vo = list.get(i);

            handleSubList(vo, rowNo);

            rowNo = fillRowWithData(row, vo, rowNo);
        }
    }

    /**
     * 判断一条数据里面是否有子项（字段是Collection） ；如果是那么需要对一下合并项数据进行处理
     * @param vo 一行数据
     */
    private void handleSubList(final T vo, final int rowNo) {
        if (!isSubList()) {
            return;
        }
        subMergedFirstRowNum = subMergedLastRowNum = rowNo;
        if (isSubListValue(vo)) {
            Collection<?> subList = getListCellValue(vo);
            subMergedLastRowNum += subList.size() - 1;
        }
    }

    /**
     * 填充数据中一行的数据，如果里面包含子项（可能会占据多行的情况）返回的rowNo 为最后一行的行号。
     * @param row 当前row
     * @param vo 数据
     * @param rowNo 当前行号
     * @return 填充完最后行的行号
     */
    private int fillRowWithData(final Row row, final T vo, final int rowNo) {
        int column = 0;
        int localRowNo = rowNo;
        for (final Object[] os : fields) {
            final Field field = (Field) os[0];
            final Excel excel = (Excel) os[1];

            if (isSubListType(field) && hasValidSubList(vo)) {
                // 这个field 存在子项列表，添加子项内容到excel中。（一个主项可能有子项并且子项可能是多条记录。如：一个项目下面的：备注field 存在多条备注内容）
                final CellAddress cellAddress = addAllSubListCells(getListCellValue(vo), row, rowNo, column);
                column += subFields.size();
                localRowNo = cellAddress.getRow();
            } else {
                // 这个field 只有主项内容（就是一个内容不会分成多行显示）
                addCell(excel, row, vo, field, column++, false);
            }
        }
        return localRowNo;
    }

    private boolean hasValidSubList(final T vo) {
        return Objects.nonNull(getListCellValue(vo));
    }


    private int calculateRowNo(final int currentIndex, final int rowNo, final int startNo) {
        if (isSubList()) {
            return currentIndex > 1 ? (rowNo + 1) : rowNo + currentIndex;
        }
        return currentIndex + 1 + rownum - startNo;
    }

    private boolean isSubListType(final Field field) {
        return Collection.class.isAssignableFrom(field.getType());
    }

    /**
     * 添加子项多行cell的数据
     * ｜            主项       ｜       子项field4     ｜ 主项  ｜
     * ｜field1｜field2 ｜field3｜r1c3｜r1c4｜r1c5｜r1c6｜field5｜
     *                         ｜r2c3｜r2c4｜r2c6｜r2c6｜
     *                         ｜r3c3｜r3c4｜r3c6｜r3c6｜
     * @param subList 多行子项的数据记录
     * @param row  当前行
     * @param rowNo 当前行号
     * @param startingColumn 子项cell开始列的index
     * @return 返回子项最后行、列的cell 的行和列地址
     */
    private CellAddress addAllSubListCells(final Collection<?> subList, final Row row, final int rowNo, final int startingColumn) {
        int endColumn = startingColumn;

        final Iterator<?> iterator = subList.iterator();

        // 第一行处理过程
        if (iterator.hasNext()) {
            endColumn = addSubListCells(iterator.next(), row, startingColumn);
        }

        // 后面多行处理过程
        int localRowNo = rowNo;
        while (iterator.hasNext()) {
            localRowNo++;
            final Row newRow = sheet.createRow(localRowNo);
            addSubListCells(iterator.next(), newRow, startingColumn);
        }

        // this.subMergedFirstRowNum += subList.size();

        return new CellAddress(localRowNo, endColumn);
    }

    /**
     * 添加子项一行cell的数据
     * ｜            主项       ｜          子项        ｜ 主项
     * ｜field1｜field2 ｜field3｜r1c3｜r1c4｜r1c5｜r1c6｜field5｜
     *                         ｜r2c3｜r2c4｜r2c6｜r2c6｜
     * @param subListItem 一行子项的数据记录
     * @param row  当前行
     * @param startingColumn 子项cell开始列的index
     * @return 返回最后列的index数字
     */
    @SuppressWarnings("unchecked")
    private int addSubListCells(final Object subListItem, final Row row, final int startingColumn) {
        final List<Field> tmpSubFields = FieldUtils.getFieldsListWithAnnotation(subListItem.getClass(), Excel.class);

        int column = startingColumn;
        for (final Field subField : tmpSubFields) {
            if (subField.isAnnotationPresent(Excel.class)) {
                final Excel attr = subField.getAnnotation(Excel.class);
                addCell(attr, row, (T) subListItem, subField, column, true);
            }
            column++;
        }
        return column;
    }

    /**
     * 创建表格样式
     *
     * @param wb 工作薄对象
     * @return 样式列表
     */
    private Map<String, CellStyle> createStyles(final Workbook wb) {
        final Map<String, CellStyle> newStyles = new HashMap<>();

        final Font titleFont = createFont(wb, (short) 16, true);
        newStyles.put("title", createCellStyle(wb, titleFont));

        final Font dataFont = createFont(wb, (short) 10, false);
        CellStyle dataStyle = createCellStyle(wb, dataFont);
        setBorders(dataStyle, IndexedColors.GREY_50_PERCENT.getIndex());
        newStyles.put("data", dataStyle);

        final Font totalFont = createFont(wb, (short) 10, false);
        newStyles.put("total", createCellStyle(wb, totalFont));

        newStyles.putAll(annotationHeaderStyles(wb, newStyles));
        newStyles.putAll(annotationDataStyles(wb));

        return newStyles;
    }

    private Font createFont(final Workbook wb, final short height, final boolean bold) {
        final Font font = wb.createFont();
        font.setFontName("Arial");
        font.setFontHeightInPoints(height);
        font.setBold(bold);
        return font;
    }

    private CellStyle createCellStyle(final Workbook wb, final Font font) {
        final CellStyle style = wb.createCellStyle();
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    private void setBorders(final CellStyle style, final short colorIndex) {
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(colorIndex);
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(colorIndex);
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(colorIndex);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(colorIndex);
    }


    /**
     * 根据Excel注解创建表格头样式
     *
     * @param wb 工作薄对象
     * @return 自定义样式列表
     */
    private Map<String, CellStyle> annotationHeaderStyles(final Workbook wb, final Map<String, CellStyle> styles) {
        return fields.stream()
            .map(os -> (Excel) os[1])
            .distinct()
            .collect(Collectors.toMap(
                excel -> String.format("header_%s_%s", excel.headerColor(), excel.headerBackgroundColor()),
                excel -> {
                    final CellStyle style = wb.createCellStyle();
                    style.cloneStyleFrom(styles.get("data"));
                    style.setAlignment(HorizontalAlignment.CENTER);
                    style.setVerticalAlignment(VerticalAlignment.CENTER);
                    style.setFillForegroundColor(excel.headerBackgroundColor().index);
                    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

                    final Font headerFont = wb.createFont();
                    headerFont.setFontName("Arial");
                    headerFont.setFontHeightInPoints((short) 10);
                    headerFont.setBold(true);
                    headerFont.setColor(excel.headerColor().index);
                    style.setFont(headerFont);

                    return style;
                },
                (existing, replacement) -> existing, // If there's a collision, keep the existing style
                HashMap::new
            ));
    }
    /**
     * 根据Excel注解创建表格列样式
     *
     * @param wb 工作薄对象
     * @return 自定义样式列表
     */
    private Map<String, CellStyle> annotationDataStyles(final Workbook wb) {
        return fields.stream()
            .map(os -> (Excel)os[1])
            .distinct()
            .collect(Collectors.toMap(
                excel -> String.format("data_%s_%s_%s", excel.align(), excel.color(), excel.backgroundColor()),
                excel -> {
                    final CellStyle style = wb.createCellStyle();
                    style.setAlignment(excel.align());
                    style.setVerticalAlignment(VerticalAlignment.CENTER);
                    style.setBorderRight(BorderStyle.THIN);
                    style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
                    style.setBorderLeft(BorderStyle.THIN);
                    style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
                    style.setBorderTop(BorderStyle.THIN);
                    style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
                    style.setBorderBottom(BorderStyle.THIN);
                    style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
                    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    style.setFillForegroundColor(excel.backgroundColor().getIndex());

                    final Font dataFont = wb.createFont();
                    dataFont.setFontName("Arial");
                    dataFont.setFontHeightInPoints((short) 10);
                    dataFont.setColor(excel.color().index);
                    style.setFont(dataFont);
                    return style;
                },
                (existing, replacement) -> existing, // If there's a collision, keep the existing style
                HashMap::new
            ));
    }

    /**
     * 创建单元格
     */
    public void createHeadCell(final Excel attr, final Row row, final int column)
    {
        // 创建列
        final Cell cell = row.createCell(column);
        // 写入列信息
        cell.setCellValue(attr.name());
        setDataValidation(attr, column);
        cell.setCellStyle(styles.get(String.format("header_%s_%s", attr.headerColor(), attr.headerBackgroundColor())));

        if (isSubList()) {
            // 填充默认样式，防止合并单元格样式失效
            sheet.setDefaultColumnStyle(column, styles.get(String.format("data_%s_%s_%s", attr.align(), attr.color(), attr.backgroundColor())));
            if (attr.needMerge()) {
                sheet.addMergedRegion(new CellRangeAddress(rownum - 1, rownum, column, column));
            }
        }
    }

    /**
     * 设置单元格信息
     *
     * @param value 单元格值
     * @param attr 注解相关
     * @param cell 单元格信息
     */
    public void setCellVo(Object value, Excel attr, Cell cell) {
        switch (attr.cellType()) {
            case STRING:
                handleStringType(value, attr, cell);
                break;
            case NUMERIC:
                handleNumericType(value, cell);
                break;
            case IMAGE:
                handleImageType(value, cell);
                break;
        }
    }

    private void handleStringType(final Object value, final Excel attr, final Cell cell) {
        String cellValue = Convert.toStr(value);
        if (StringUtils.startsWithAny(cellValue, FORMULA_STR)) {
            cellValue = RegExUtils.replaceFirst(cellValue, FORMULA_REGEX_STR, "\t$0");
        }
        if (value instanceof Collection && StringUtils.equals("[]", cellValue)) {
            cellValue = StringUtils.EMPTY;
        }
        cell.setCellValue(Objects.isNull(cellValue) ? attr.defaultValue() : cellValue + attr.suffix());
    }

    private void handleNumericType(final Object value, final Cell cell) {
        if (Objects.nonNull(value)) {
            cell.setCellValue(
                StringUtils.contains(Convert.toStr(value), ".") ? Convert.toDouble(value) : Convert.toInt(value)
            );
        }
    }

    private void handleImageType(final Object value, final Cell cell) {
        final ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0,
            (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
        final String imagePath = Convert.toStr(value);
        if (StringUtils.isNotEmpty(imagePath)) {
            final byte[] data = ImageUtils.getImage(imagePath);
            getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
                cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
        }
    }



    /**
     * 获取画布
     */
    public static Drawing<?> getDrawingPatriarch(final Sheet sheet) {
        if (sheet.getDrawingPatriarch() == null) {
            sheet.createDrawingPatriarch();
        }
        return sheet.getDrawingPatriarch();
    }

    /**
     * 获取图片类型,设置图片插入类型
     */
    public int getImageType(final byte[] value)
    {
        final String imageType = FileTypeUtils.getFileExtendName(value);
        if ("JPG".equalsIgnoreCase(imageType)) {
            return Workbook.PICTURE_TYPE_JPEG;
        } else if ("PNG".equalsIgnoreCase(imageType)) {
            return Workbook.PICTURE_TYPE_PNG;
        }
        return Workbook.PICTURE_TYPE_JPEG;
    }

    /**
     * 创建表格样式
     */
    public void setDataValidation(final Excel attr, final int column)
    {
        if (attr.name().contains("注：")) {
            sheet.setColumnWidth(column, 6000);
        } else {
            // 设置列宽
            sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
        }
        if (StringUtils.isNotEmpty(attr.prompt()) || attr.combo().length > 0) {
            if (attr.combo().length > 15 || StringUtils.join(attr.combo()).length() > 255) {
                // 如果下拉数大于15或字符串长度大于255，则使用一个新sheet存储，避免生成的模板下拉值获取不到
                setXSSFValidationWithHidden(sheet, attr.combo(), attr.prompt(), new CellRange(1, 100, column, column));
            } else {
                // 提示信息或只能选择不能输入的列内容.
                setPromptOrValidation(sheet, attr.combo(), attr.prompt(), new CellRange(1, 100, column, column));
            }
        }
    }

    /**
     * 添加单元格
     */
    public void addCell(final Excel attr, final Row row, final T vo, final Field field, final int column , final boolean isSub) {
        try {
            if (!attr.isExport()) {
                return;
            }

            row.setHeight(maxHeight);
            final Cell cell = row.createCell(column);
            // 在处理sublist 的cell 的时候不需要处理这个项。
            if(!isSub) {
                setCellMergeRegionIfRequired(vo, attr, column);
            }
            cell.setCellStyle(getCellStyle(attr));

            final Object value = getTargetValue(vo, field, attr);
            setCellValue(cell, value, attr);

            addStatisticsData(column, Convert.toStr(value), attr);
        } catch (final Exception e) {
            LOGGER.error("Excel 添加单元格 失败 row:{} column:{}", row.getRowNum(), column, e);
        }
    }

    private void setCellMergeRegionIfRequired(final T vo, final Excel attr, final int column) {
        if (isSubListValue(vo) && getListCellValue(vo).size() > 1 && attr.needMerge()) {
            // 如果有多行集合对象，进行合并单元格。
            CellRangeAddress cellAddress = new CellRangeAddress(subMergedFirstRowNum, subMergedLastRowNum, column, column);
            sheet.addMergedRegion(cellAddress);
        }
    }

    private CellStyle getCellStyle(final Excel attr) {
        return styles.get(String.format("data_%s_%s_%s", attr.align(), attr.color(), attr.backgroundColor()));
    }

    private void setCellValue(final Cell cell, final Object value, final Excel attr) {
        if (value == null) {
            cell.setCellValue("");
            return;
        }

        if (StringUtils.isNotEmpty(attr.dateFormat())) {
            cell.setCellValue(parseDateToStr(attr.dateFormat(), value));
        } else if (StringUtils.isNotEmpty(attr.readConverterExp())) {
            cell.setCellValue(convertByExp(Convert.toStr(value), attr.readConverterExp(), attr.separator()));
        } else if (StringUtils.isNotEmpty(attr.dictType())) {
            cell.setCellValue(getDictLabel(value, attr.dictType(), attr.separator()));
        } else if (value instanceof BigDecimal && -1 != attr.scale()) {
            cell.setCellValue(((BigDecimal) value).setScale(attr.scale(), attr.roundingMode()).doubleValue());
        } else if (!attr.handler().equals(ExcelHandlerAdapter.class)) {
            cell.setCellValue(dataFormatHandlerAdapter(value, attr));
        } else {
            setCellVo(value, attr, cell);
        }
    }

    private String getDictLabel(final Object value, final String dictType, final String separator) {
        final String key = dictType + value;
        if (!sysDictMap.containsKey(key)) {
            final String label = convertDictByExp(Convert.toStr(value), dictType, separator);
            sysDictMap.put(key, label);
        }
        return sysDictMap.get(key);
    }



    /**
     * 设置 POI XSSFSheet 单元格提示或选择框
     *
     * @param sheet 表单
     * @param textlist 下拉框显示的内容
     * @param promptContent 提示内容
     * @param cellRange 开始行 结束行 开始列 结束列
     */
    public void setPromptOrValidation(Sheet sheet, String[] textlist, String promptContent,  final CellRange cellRange)
    {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = textlist.length > 0 ? helper.createExplicitListConstraint(textlist) : helper.createCustomConstraint("DD1");
        addValidationToSheet(sheet, promptContent, cellRange, helper, constraint);
    }

    /**
     * 设置某些列的值只能输入预制的数据,显示下拉框（兼容超出一定数量的下拉框）.
     *
     * @param sheet 要设置的sheet.
     * @param textlist 下拉框显示的内容
     * @param promptContent 提示内容
     * @param cellRange 开始行 结束行 开始列 结束列
     */
    public void setXSSFValidationWithHidden(final Sheet sheet, final String[] textlist, final String promptContent, final CellRange cellRange)
    {
        final String hideSheetName = "combo_" + cellRange.getFirstCol() + "_" + cellRange.getEndCol();
        final Sheet hideSheet = wb.createSheet(hideSheetName); // 用于存储 下拉菜单数据

        for (int i = 0; i < textlist.length; i++) {
            hideSheet.createRow(i).createCell(0).setCellValue(textlist[i]);
        }

        // 创建名称，可被其他单元格引用
        final Name name = wb.createName();
        name.setNameName(hideSheetName + "_data");
        name.setRefersToFormula(hideSheetName + "!$A$1:$A$" + textlist.length);

        final DataValidationHelper helper = sheet.getDataValidationHelper();
        // 加载下拉列表内容
        final DataValidationConstraint constraint = helper.createFormulaListConstraint(hideSheetName + "_data");
        addValidationToSheet(sheet, promptContent, cellRange, helper, constraint);

        // 设置hiddenSheet隐藏
        wb.setSheetHidden(wb.getSheetIndex(hideSheet), true);
    }

    private static void addValidationToSheet(final Sheet sheet, final String promptContent, final CellRange cellRange,
                                             final DataValidationHelper helper, final DataValidationConstraint constraint) {
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        final CellRangeAddressList regions = new CellRangeAddressList(cellRange.getFirstRow(), cellRange.getEndRow(),
            cellRange.getFirstCol(), cellRange.getEndCol());
        // 数据有效性对象
        final DataValidation dataValidation = helper.createValidation(constraint, regions);
        if (StringUtils.isNotEmpty(promptContent)) {
            // 如果设置了提示信息则鼠标放上去提示
            dataValidation.createPromptBox("", promptContent);
            dataValidation.setShowPromptBox(true);
        }
        // 处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }

        sheet.addValidationData(dataValidation);
    }

    /**
     * 解析导出值 0=男,1=女,2=未知
     *
     * @param propertyValue 参数值
     * @param converterExp 翻译注解
     * @param separator 分隔符
     * @return 解析后值
     */
    public static String convertByExp(final String propertyValue, final String converterExp, final String separator) {
        // 创建映射
        final Map<String, String> keyToValueMap = Arrays.stream(StringUtils.split(converterExp, ","))
            .map(item -> StringUtils.split(item, "="))
            .collect(Collectors.toMap(arr -> arr[0], arr -> arr[1], (k1, k2) -> k1));

        return convertByMap(propertyValue, keyToValueMap, separator);
    }

    /**
     * 反向解析值 男=0,女=1,未知=2
     *
     * @param propertyValue 参数值 如（0，1）
     * @param converterExp 翻译注解 如（男=0,女=1,未知=2）
     * @param separator 分隔符
     * @return 解析后值 如（男，女）
     */
    public static String reverseByExp(final String propertyValue, final String converterExp, final String separator) {
        // 创建映射
        final Map<String, String> valueToKeyMap = Arrays.stream(StringUtils.split(converterExp, ","))
            .map(item -> StringUtils.split(item, "="))
            .collect(Collectors.toMap(arr -> arr[1], arr -> arr[0], (k1, k2) -> k1));
        return convertByMap(propertyValue, valueToKeyMap, separator);
    }
    public static String convertByMap(final String propertyValue, final Map<String, String> converterMap, final String separator) {
        if (StringUtils.contains(propertyValue, separator)) {
            // 处理多个值的情况
            return Arrays.stream(StringUtils.split(propertyValue, separator))
                .map(value -> converterMap.getOrDefault(value, ""))
                .filter(StringUtils::isNotEmpty)
                .collect(Collectors.joining(separator));
        } else {
            // 处理单个值的情况
            return converterMap.getOrDefault(propertyValue, "");
        }
    }

    /**
     * 解析字典值
     *
     * @param dictValue 字典值
     * @param dictType 字典类型
     * @param separator 分隔符
     * @return 字典标签
     */
    public static String convertDictByExp(final String dictValue, final String dictType, final String separator) {
        return DictUtils.getDictLabel(dictType, dictValue, separator);
    }

    /**
     * 反向解析值字典值
     *
     * @param dictLabel 字典标签
     * @param dictType 字典类型
     * @param separator 分隔符
     * @return 字典值
     */
    public static String reverseDictByExp(final String dictLabel, final String dictType, final String separator) {
        return DictUtils.getDictValue(dictType, dictLabel, separator);
    }

    /**
     * 自定义 数据处理器
     *
     * @param value 数据值
     * @param excel 数据注解
     * @return 返回对应的值
     */
    public String dataFormatHandlerAdapter(final Object value, final Excel excel)
    {
        try {
            final Object instance = excel.handler().getDeclaredConstructor().newInstance();
            final Method formatMethod = excel.handler().getMethod("format", Object.class, String[].class);
            return Convert.toStr(formatMethod.invoke(instance, value, excel.args()));
        } catch (final Exception e) {
            LOGGER.error("不能格式化数据{} ", excel.handler(), e);
        }
        return Convert.toStr(value);
    }

    /**
     * 合计统计信息
     */
    private void addStatisticsData(final Integer index, final String text, final Excel entity) {
        if (entity != null && entity.isStatistics()) {
            statistics.putIfAbsent(index, 0D);
            Double temp = 0D;
            try {
                temp = Double.valueOf(text);
            } catch (final NumberFormatException e) {
                LOGGER.error("数据转换出错{}", text, e);
            }
            statistics.put(index, statistics.get(index) + temp);
        }
    }

    /**
     * 创建统计行
     */
    public void addStatisticsRow() {
        if (statistics.isEmpty()) {
            return;
        }
        final Row row = sheet.createRow(sheet.getLastRowNum() + 1);
        final Set<Integer> keys = statistics.keySet();
        final Cell cell = row.createCell(0);
        cell.setCellStyle(styles.get("total"));
        cell.setCellValue("合计");

        for (final Integer key : keys) {
            final Cell tmpCell = row.createCell(key);
            tmpCell.setCellStyle(styles.get("total"));
            tmpCell.setCellValue(DOUBLE_FORMAT.format(statistics.get(key)));
        }
        statistics.clear();

    }

    /**
     * 编码文件名
     */
    public String encodingFilename(final String filename) {
        return UUID.randomUUID() + "_" + filename + ".xlsx";
    }

    /**
     * 获取下载路径，如果上层目录没有创建那么创建上层目录
     *
     * @param filename 文件名称
     * @return 文件路径
     */
    public String getAbsoluteFile(final String filename) {
        final String downloadPath = FileUtils.getDefaultDownloadPath() + filename;
        final File desc = new File(downloadPath);
        if (!desc.getParentFile().exists() && !desc.getParentFile().mkdirs()) {
            // 创建失败
            LOGGER.error("目录创建失败:{}", downloadPath);
        }
        return downloadPath;
    }

    /**
     * 获取bean中的属性值
     *
     * @param vo 实体对象
     * @param field 字段
     * @param excel 注解
     * @return 最终的属性值
     * @throws IllegalAccessException, NoSuchFieldException
     */
    private Object getTargetValue(final T vo, final Field field, final Excel excel) throws IllegalAccessException, NoSuchFieldException {
        final String target = excel.targetAttr();
        ReflectionUtils.makeAccessible(field);
        Object obj = field.get(vo);

        if (StringUtils.isNotBlank(target)) {
            // 用点分割目标，并遍历每个属性
            for (final String name : target.split("\\.")) {
                obj = getValue(obj, name);
            }
        }
        return obj;
    }


    /**
     * 以类的属性的get方法方法形式获取值
     *
     * @param obj 对象
     * @param name 属性名
     * @return value 属性的值
     * @throws NoSuchFieldException, IllegalAccessException
     */
    private Object getValue(final Object obj, final String name) throws NoSuchFieldException, IllegalAccessException {
        if (Objects.nonNull(obj) && StringUtils.isNotEmpty(name)) {
            final Class<?> tmpClazz = obj.getClass();
            final Field field = tmpClazz.getDeclaredField(name);
            // field.setAccessible(true); 能够允许反射对象修改访问权修饰符，绕过由Java访问修饰符提供的访问控制检查，它让程序员能够更改私有字段或调用稀有方法，
            // 会被安全漏洞扫描，采用下面的方法（spring针对反射提供的工具类）。
            ReflectionUtils.makeAccessible(field);
            return field.get(obj);
        }
        return obj;
    }

    /**
     * 得到所有定义字段
     */
    private void createExcelField()
    {
        this.fields = getFields();
        this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort())).collect(Collectors.toList());
        this.maxHeight = getRowHeight();
    }

    /**
     * 获取字段注解信息
     */
    public List<Object[]> getFields() {
        final List<Field> allFields = new ArrayList<>();
        allFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
        allFields.addAll(Arrays.asList(clazz.getDeclaredFields()));

        return allFields.stream()
            .filter(field -> !ArrayUtils.contains(this.excludeFields, field.getName()))
            .flatMap(this::getFieldWithAnnotations)
            .collect(Collectors.toList());
    }

    private Stream<Object[]> getFieldWithAnnotations(final Field field) {
        final List<Object[]> fieldsList = new ArrayList<>();

        if (field.isAnnotationPresent(Excel.class)) {
            final Excel attr = field.getAnnotation(Excel.class);
            if (shouldProcessExcelAnnotation(attr)) {
                ReflectionUtils.makeAccessible(field);
                fieldsList.add(new Object[]{field, attr});
                handleCollectionField(field);
            }
        }

        if (field.isAnnotationPresent(Excels.class)) {
            final Excels attrs = field.getAnnotation(Excels.class);
            Arrays.stream(attrs.value())
                .filter(this::shouldProcessExcelAnnotation)
                .forEach(attr -> {
                    if (!ArrayUtils.contains(this.excludeFields, field.getName() + "." + attr.targetAttr())) {
                        ReflectionUtils.makeAccessible(field);
                        fieldsList.add(new Object[]{field, attr});
                    }
                });
        }

        return fieldsList.stream();
    }

    private boolean shouldProcessExcelAnnotation(final Excel attr) {
        return attr != null && (attr.type() == Type.ALL || attr.type() == type);
    }

    private void handleCollectionField(final Field field) {
        if (Collection.class.isAssignableFrom(field.getType())) {//是否是Collection类型或子类型
            subMethod = getSubMethod(field.getName(), clazz);
            final ParameterizedType pt = (ParameterizedType) field.getGenericType();
            final Class<?> subClass = (Class<?>) pt.getActualTypeArguments()[0];
            this.subFields = FieldUtils.getFieldsListWithAnnotation(subClass, Excel.class);
        }
    }


    /**
     * 根据注解获取最大行高
     */
    public short getRowHeight()
    {
        final double maxRowHeight = this.fields.stream()
            .map(array -> (Excel)array[1])
            .mapToDouble(Excel::height)
            .max()
            .orElse(0);

        return (short) (maxRowHeight * 20);
    }

    /**
     * 创建一个工作簿
     */
    public void createWorkbook()
    {
        this.wb = new SXSSFWorkbook(500);
        this.sheet = wb.createSheet();
        wb.setSheetName(0, sheetName);
        this.styles = createStyles(wb);
    }

    /**
     * 创建工作表
     *
     * @param sheetNo sheet数量
     * @param index 序号
     */
    public void createSheet(final int sheetNo, final int index)
    {
        // 设置工作表的名称.
        if (sheetNo > 1 && index > 0)
        {
            this.sheet = wb.createSheet();
            this.createTitle();
            wb.setSheetName(index, sheetName + index);
        }
    }

    /**
     * 获取单元格值
     *
     * @param row 获取的行
     * @param column 获取单元格列号
     * @return 单元格值
     */
    public Object getCellValue(final Row row, final int column) {
        if (row == null) {
            return null;
        }

        final Cell cell = row.getCell(column);
        if (cell == null) {
            return "";
        }

        return getCellValue(cell);
    }
    private Object getCellValue(final Cell cell) {
        try {
            switch (cell.getCellType()) {
                case NUMERIC:
                    return formatNumericCellValue(cell);
                case STRING:
                    return cell.getStringCellValue();
                case BOOLEAN:
                    return cell.getBooleanCellValue();
                case ERROR:
                    return cell.getErrorCellValue();
                case FORMULA:
                    return formatFormulaCellValue(cell);
                default:
                    return "";
            }
        } catch (final Exception e) {
            LOGGER.error("Error  cell type :{} ",cell.getCellType() , e);
            return "";
        }
    }
    private Object formatNumericCellValue(final Cell cell) {
        final double numericValue = cell.getNumericCellValue();

        if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
            return org.apache.poi.ss.usermodel.DateUtil.getJavaDate(numericValue);
        }

        if (numericValue % 1 != 0) {
            return new BigDecimal(Double.toString(numericValue));
        } else {
            return new DecimalFormat("0").format(numericValue);
        }
    }

    private Object formatFormulaCellValue(final Cell cell) {
        switch (cell.getCachedFormulaResultType()) {
            case NUMERIC:
                return formatNumericCellValue(cell);
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case ERROR:
                return cell.getErrorCellValue();
            default:
                return cell.getCellFormula();
        }
    }



    /**
     * 判断是否是空行
     *
     * @param row 判断的行
     * @return 是否是空行
     */
    private boolean isRowEmpty(final Row row) {
        if (row == null) {
            return true;
        }

        return IntStream.range(row.getFirstCellNum(), row.getLastCellNum())
            .mapToObj(row::getCell)
            .allMatch(cell -> cell == null || cell.getCellType() == CellType.BLANK);
    }


    /**
     * 获取Excel2003图片
     *
     * @param sheet 当前sheet对象
     * @param workbook 工作簿对象
     * @return Map key:图片单元格索引（1_1）String，value:图片流PictureData
     */
    public static Map<String, PictureData> getSheetPictures03(final HSSFSheet sheet, final HSSFWorkbook workbook)
    {
        final Map<String, PictureData> sheetIndexPicMap = new HashMap<>();
        final List<HSSFPictureData> pictures = workbook.getAllPictures();
        if (!pictures.isEmpty()) {
            sheet.getDrawingPatriarch().getChildren().stream()
                .filter(HSSFPicture.class::isInstance)
                .forEach(shape -> {// 也可以用 Collectors.toMap 来做，后一个方法
                    final HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
                    final HSSFPicture pic = (HSSFPicture) shape;
                    final int pictureIndex = pic.getPictureIndex() - 1;
                    final HSSFPictureData picData = pictures.get(pictureIndex);
                    final String picIndex = anchor.getRow1() + "_" + anchor.getCol1();
                    sheetIndexPicMap.put(picIndex, picData);
                });
        }
        return sheetIndexPicMap;
    }

    /**
     * 获取Excel2007图片
     *
     * @param sheet 当前sheet对象
     * @return Map key:图片单元格索引（1_1）String，value:图片流PictureData
     */
    public static Map<String, PictureData> getSheetPictures07(final XSSFSheet sheet) {
        return sheet.getRelations().stream()
            .filter(XSSFDrawing.class::isInstance)
            .flatMap(rel -> ((XSSFDrawing) rel).getShapes().stream())
            .filter(XSSFPicture.class::isInstance)
            .map(XSSFPicture.class::cast)
            .collect(Collectors.toMap(
                pic -> {
                    CTMarker ctMarker = pic.getPreferredSize().getFrom();
                    return ctMarker.getRow() + "_" + ctMarker.getCol();
                },
                XSSFPicture::getPictureData,
                (existing, replacement) -> existing, // 如果map 的可以重复 保留已经存在的
                LinkedHashMap::new
            ));
    }


    /**
     * 格式化不同类型的日期对象
     *
     * @param dateFormat 日期格式
     * @param val 被格式化的日期对象
     * @return 格式化后的日期字符
     */
    public String parseDateToStr(final String dateFormat, final Object val) {
        if (val == null) {
            return "";
        }

        final Date dateToFormat = extractDateFromObject(val);

        if (dateToFormat != null) {
            return DateUtil.parseDateToStr(dateFormat, dateToFormat);
        }

        return val.toString();
    }

    /**
     * 各种时间类型转 Date
     * @param obj 时间
     * @return Date日期
     */
    private Date extractDateFromObject(final Object obj) {
        if (obj instanceof Date) {
            return (Date) obj;
        } else if (obj instanceof LocalDateTime) {
            return DateUtil.localDateTimetoDate((LocalDateTime) obj);
        } else if (obj instanceof LocalDate) {
            return DateUtil.localDatetoDate((LocalDate) obj);
        }
        return null;
    }



    /**
     * 是否有对象的子列表
     */
    public boolean isSubList()
    {
        return CollectionUtils.isNotEmpty(subFields);
    }

    /**
     * 是否有对象的子列表，集合不为空
     */
    public boolean isSubListValue(final T vo)
    {
        final Collection<?> listCellValue = getListCellValue(vo);
        return CollectionUtils.isNotEmpty(subFields) && CollectionUtils.isNotEmpty(listCellValue);
    }

    /**
     * 获取集合的值
     */
    public Collection<?> getListCellValue(final Object obj) {
        if (subMethod == null) {
            return Collections.emptyList();
        }
        try {
            final Object value = subMethod.invoke(obj);
            if (value instanceof Collection<?>) {
                return (Collection<?>) value;
            }
        } catch (final Exception e) {
            LOGGER.error("Failed to invoke method on object: {}", obj, e);
        }
        return Collections.emptyList();  // Return an immutable empty list if the value is not a collection
    }

    /**
     * 获取对象的子列表方法
     *
     * @param name 名称
     * @param pojoClass 类对象
     * @return 子列表方法
     */
    public Method getSubMethod(final String name, final Class<?> pojoClass) {
        if (name == null || name.isEmpty()) {
            return null;
        }

        final StringBuilder getMethodName = new StringBuilder("get");
        getMethodName.append(Character.toUpperCase(name.charAt(0)));
        getMethodName.append(name.substring(1));

        try {
            return pojoClass.getMethod(getMethodName.toString(), (Class<?>[]) null);
        } catch (NoSuchMethodException e) {
            LOGGER.error("No such method: {} in class: {}", getMethodName, pojoClass.getName(), e);
        } catch (Exception e) {
            LOGGER.error("Failed to get method: {} from class: {}", getMethodName, pojoClass.getName(), e);
        }

        return null;
    }

}
